package com.xmy.cultivate.mapper;

import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import com.xmy.cultivate.entity.Course;
import com.xmy.cultivate.entity.OrderDetail;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.xmy.cultivate.entity.Organization;
import com.xmy.cultivate.entity.Subjects;
import com.xmy.cultivate.entity.views.*;
import com.xmy.cultivate.respons.IncomeDailyRs;
import io.swagger.models.auth.In;
import org.apache.ibatis.annotations.*;

import java.math.BigDecimal;
import java.util.List;
import java.util.Map;

/**
 * <p>
 * 订单报名信息 Mapper 接口
 * </p>
 *
 * @author min
 * @since 2022-08-01
 */
@Mapper
public interface OrderDetailMapper extends BaseMapper<OrderDetail> {


    /**
     * 报读列表
     * @return
     */
    @Select("select * from order_detail ${ew.customSqlSegment}")
    public abstract IPage<ResApplyGather> getOrderDetailListForPage(IPage<?> page, @Param(Constants.WRAPPER) Wrapper wrapper);

    @Select("select * from order_detail ${ew.customSqlSegment}")
    public abstract List<ResApplyGather> getOrderDetailList(@Param(Constants.WRAPPER) Wrapper wrapper);

    /**
     * 报读列表
     * @return
     */
    @Select("select * from enroll_view ${ew.customSqlSegment}")
    public abstract IPage<EnrollList> getEnrollList(IPage<?> page, @Param(Constants.WRAPPER) Wrapper wrapper);


    @Select("select sum(solid_price) as solid_price from enroll_view ${ew.customSqlSegment}")
    public abstract BigDecimal getSolidPrice(@Param(Constants.WRAPPER) Wrapper wrapper);

    @Select("select count(DISTINCT student_id) as count  from enroll_view ${ew.customSqlSegment}")
    public abstract BigDecimal getAllStudentCount(@Param(Constants.WRAPPER) Wrapper wrapper);


    @Select("select id,order_id,all_course_count,use_course_count,residue_course_count,use_price,out_price,validity_date,price,solid_price,discounts_price,use_price from order_detail ${ew.customSqlSegment}")
    public abstract List<OrderDetail> orderDetail(@Param(Constants.WRAPPER) Wrapper wrapper);


    @Select("select id,order_id,all_course_count,use_course_count,residue_course_count,use_price,out_price,validity_date,price,solid_price,discounts_price,use_price from order_detail where school_id = #{schoolId} and student_id = #{studentId} and course_id = #{courseId} and deleted = 0 and status<>2 and residue_course_count>0")
    public abstract List<OrderDetail> orderDetailListForCourse(Long schoolId,Long studentId,Long courseId);


    //由于要纠正数据，等于0的限制暂时取消，后面恢复，and residue_course_count>0
    @Select("select id,order_id,course_price_id,all_course_count,use_course_count,residue_course_count,use_price,out_price,validity_date,price,solid_price,discounts_price,residue_price,student_id from order_detail where school_id = #{schoolId} and student_id = #{studentId} and subjects_id = #{subjectsId} and deleted = 0 and status<>2 and residue_course_count>0 and year_and_quarter>=${yearAndQuarter} and lesson_type=${lessonType} order by year_and_quarter,id asc")
    public abstract List<OrderDetail> orderDetailListForSubjects(@Param("schoolId")Long schoolId,@Param("studentId") Long studentId,@Param("subjectsId") Long subjectsId,@Param("yearAndQuarter") Integer yearAndQuarter,@Param("lessonType")Integer lessonType);



    //public abstract List<Map<String,Object>> orderDetail(@Param(Constants.WRAPPER) Wrapper wrapper);

    /**
     * 获取该科目是否报读过
     * @param studentId
     * @param subjectsId
     * @return
     */
    @Select("select count(a.id) as count from order_detail as a left join order_apply as b on a.order_id = b.id left join course as c on a.course_id = c.id where a.school_id = #{schoolId} and a.student_id = #{studentId} and c.subjects_id = #{subjectsId} and b.status <> 2 and a.deleted = 0 and a.lesson_type =${lessonType} and ((a.year_part<#{newYeartPart}) or (a.year_part=#{newYeartPart} and a.quarter_num<=#{newNum})) and a.reading_status<>3")
    public  abstract  Integer getApplyCountForSubjects(@Param("schoolId") Long schoolId,@Param("studentId") Long studentId,@Param("subjectsId") Long subjectsId,@Param("newYeartPart") Integer newYeartPart,@Param("newNum") Integer newNum,@Param("lessonType") Integer lessonType);

    @Select("select a.id,a.year_part as yearPart,a.is_new_student as isNewStudent,c.quarter_num as quarterNum from order_detail as a left join order_apply as b on a.order_id = b.id left join course as c on a.course_id = c.id where a.school_id = #{schoolId} and a.student_id = #{studentId} and c.subjects_id = #{subjectsId} and b.status <> 2 and a.deleted = 0 and ((a.year_part<#{newYeartPart}) or (a.year_part=#{newYeartPart} and a.quarter_num<=#{newNum})) and a.reading_status<>3 and a.lesson_type=${lessonType} ORDER BY a.created_at desc limit 0,1 ")
    public  abstract  Map<String,Object> getApplyCourseForSubjects(@Param("schoolId") Long schoolId,@Param("studentId") Long studentId,@Param("subjectsId") Long subjectsId,@Param("newYeartPart") Integer newYeartPart,@Param("newNum") Integer newNum,@Param("lessonType")Integer lessonType);


    /**
     * 获取某科目最后一次报读的数据
     * @param studentId
     * @param subjectsId
     * @return
     */
    @Select("select a.* from order_detail as a left join order_apply as b on a.order_id = b.id left join course as c on a.course_id = c.id where a.year_part = ${yearPart} and a.school_id = ${schoolId} and a.quarter_num = ${quarterNum} and a.student_id = #{studentId} and a.subjects_id=#{subjectsId} and b.status <> 2 and a.deleted = 0 and a.lesson_type =${lessonType} order by a.id desc limit 0,1")
    @Results({
            @Result(column = "id",property = "id"),
            @Result(
                    property = "course",
                    column = "course_id",
                    javaType = Course.class,
                    one = @One(select = "com.xmy.cultivate.mapper.CourseMapper.selectById")
            )
    })
    public abstract OrderDetail getLastOneForSubjectId(@Param("schoolId") Long schoolId,@Param("studentId") Long studentId,@Param("subjectsId") Long subjectsId,@Param("yearPart") Integer yearPart,@Param("quarterNum") Integer quarterNum,@Param("lessonType")Integer lessonType);


    /**
     * 获取所报读科目的剩余课时
     * @param schoolId
     * @param studentId
     * @param subjectsId
     * @return
     */
    @Select({
            "<script>",
            "select ifnull(sum(residue_course_count),0) as residue_course_count from order_detail where deleted=0 and status &lt;&gt; 2 ",
            "<when test='schoolId!=null'>",
            "AND school_id = ${schoolId}",
            "</when>",
            "and student_id=#{studentId} and subjects_id=#{subjectsId} and residue_course_count>0 and year_and_quarter>=${yearAndQuarter} and lesson_type=${lessonType}",
            "</script>"
    })
    public abstract Integer getResidueCourseCountForSubjects(@Param("schoolId") Long schoolId,@Param("studentId") Long studentId,@Param("subjectsId") Long subjectsId,@Param("yearAndQuarter") Integer yearAndQuarter,@Param("lessonType") Integer lessonType);

    /**
     * 不分校区
     * 获取所报读科目的剩余课时
     * @param studentId
     * @param subjectsId
     * @return
     */
    @Select("select ifnull(sum(residue_course_count),0) as residue_course_count from order_detail where deleted=0 and status<>2 and student_id=#{studentId} and subjects_id=#{subjectsId} and residue_course_count>0 and lesson_type =${lessonType}")
    public abstract Integer getResidueCourseCountForSubjectsAll(@Param("studentId") Long studentId,@Param("subjectsId") Long subjectsId,@Param("lessonType") Integer lessonType);


    /**
     * 获取报读课程的最后一条数据
     * @param schoolId
     * @param studentId
     * @param courseId
     * @return
     */
    @Select("select a.* from order_detail as a where a.school_id = #{schoolId}  a.student_id = #{studentId} and a.course_id=#{courseId} and b.status <> 2 and a.deleted = 0 and a.residue_course_count>0 and a.lesson_type=${lessonType} order by a.id desc limit 0,1")
    @Results({
            @Result(column = "id",property = "id"),
            @Result(
                    property = "course",
                    column = "course_id",
                    javaType = Course.class,
                    one = @One(select = "com.xmy.cultivate.mapper.CourseMapper.selectById")
            )
    })
    public abstract OrderDetail getLastOneForCourseId(@Param("schoolId") Long schoolId,@Param("studentId") Long studentId,@Param("courseId") Long courseId,@Param("lessonType") Integer lessonType);


    /**
     * 获取科目的总课次
     * @param schoolId
     * @param studentId
     * @param subjectsId
     * @param yearAndQuarter
     * @return
     */
    @Select("select IFNULL(sum(a.all_course_count/3),0) as allCourseCount  from order_detail as a left join grade as b on a.grade_id = b.id where a.school_id= #{schoolId} and a.student_id = #{studentId} and a.subjects_id = #{subjectsId} and a.status <>2 and a.year_and_quarter >= #{yearAndQuarter} and a.lesson_type=${lessonType} and a.deleted = 0 ")
    public abstract Integer getAllCourseCountForSubjects(@Param("schoolId") Long schoolId,@Param("studentId") Long studentId,@Param("subjectsId") Long subjectsId,@Param("yearAndQuarter") Integer yearAndQuarter,@Param("lessonType") Integer lessonType);



    /**
     * 获取科目的剩余课次
     * @param schoolId
     * @param studentId
     * @param subjectsId
     * @param yearAndQuarter
     * @return
     */
    @Select("select IFNULL(sum(a.residue_course_count/3),0) as residueCourseCount  from order_detail as a left join grade as b on a.grade_id = b.id where a.school_id= #{schoolId} and a.student_id = #{studentId} and a.subjects_id = #{subjectsId} and a.status <>2 and a.year_and_quarter >= #{yearAndQuarter} and a.lesson_type=${lessonType} and a.deleted = 0 ")
    public abstract Integer getResidueCourseCount(@Param("schoolId") Long schoolId,@Param("studentId") Long studentId,@Param("subjectsId") Long subjectsId,@Param("yearAndQuarter") Integer yearAndQuarter,@Param("lessonType") Integer lessonType);

    /**
     * 获取科目的剩余金额
     * @param schoolId
     * @param studentId
     * @param subjectsId
     * @param yearAndQuarter
     * @return
     */
    @Select("select IFNULL(sum(a.residue_price),0) as residue_rice  from order_detail as a where a.school_id= #{schoolId} and a.student_id = #{studentId} and a.subjects_id = #{subjectsId} and a.status <>2 and a.year_and_quarter >= #{yearAndQuarter} and a.lesson_type=${lessonType} and a.deleted = 0 ")
    public abstract BigDecimal getAllResiduePrice(@Param("schoolId") Long schoolId,@Param("studentId") Long studentId,@Param("subjectsId") Long subjectsId,@Param("yearAndQuarter") Integer yearAndQuarter,@Param("lessonType") Integer lessonType);



    /**
     * 获取所报读班级的剩余课时
     * @return
     */
    @Select("select sum(residue_course_count) as residue_course_count,sum(residue_price) as residue_price from order_detail as a where id in (select order_detail_id from student_grade as b where b.student_id=#{studentId} and b.grade_id=#{gradeId} and b.status =1 and b.reading_status=1 and b.school_id=#{schoolId}) and a.reading_status = 1 and a.lesson_type=${lessonType} and a.deleted = 0")
    public abstract OrderDetailTiny getResidueCourseCountForGrade(@Param("studentId") Long studentId,@Param("gradeId") Long gradeId,@Param("schoolId") Long schoolId,@Param("lessonType") Integer lessonType);

    /**
     * 获取所报读课程的剩余课时
     * @return
     */
    @Select("select sum(residue_course_count) as residue_course_count,sum(all_course_count) as all_course_count,sum(use_course_count) as use_course_count,sum(use_price) as use_price from order_detail as a where student_id=#{studentId} and course_id=#{courseId} and a.reading_status = 1 and a.school_id = #{schoolId} and a.deleted = 0 and a.status<>2 and a.lesson_type=${lessonType}")
    public abstract OrderDetailTiny getResidueCourseCountForCourseId(@Param("studentId") Long studentId,@Param("courseId") Long courseId,@Param("schoolId") Long schoolId,@Param("lessonType") Integer lessonType);

    /**
     * 获取所报读科目的剩余课时
     * @return
     */
    @Select("select sum(residue_course_count) as residue_course_count,sum(all_course_count) as all_course_count,sum(use_course_count) as use_course_count,sum(use_price) as use_price from order_detail as a where student_id=#{studentId} and subjects_id=#{subjectsId} and a.reading_status = 1 and a.school_id = #{schoolId} and a.deleted = 0 and a.status<>2 and a.lesson_type=${lessonType}")
    public abstract OrderDetailTiny getResidueCourseCountForSubjectsId(@Param("studentId") Long studentId,@Param("subjectsId") Long subjectsId,@Param("schoolId") Long schoolId,@Param("lessonType") Integer lessonType);



    /**
     * 获取所报读课程的剩余课时
     * @return
     */
    @Select("select sum(residue_course_count) as residue_course_count from order_detail as a where student_id=#{studentId} and course_id=#{courseId} and a.reading_status = 1 and a.school_id = #{schoolId} and a.deleted = 0 and a.status<>2 and a.lesson_type=${lessonType}")
    public abstract Integer getResidueCourseHouseForCourseId(@Param("studentId") Long studentId,@Param("courseId") Long courseId,@Param("schoolId") Long schoolId,@Param("lessonType") Integer lessonType);

    /**
     * 办理中心获取学员报读信息
     * @return
     */
    //@Select("select a.* from order_detail as a LEFT JOIN order_apply as b on a.order_id = b.id where b.`status` <> 2  and a.residue_course_count > 0 and a.student_id = #{studentId} and a.deleted=0  group by course_id order by a.id desc ")
    @Select("select a.* from order_detail as a LEFT JOIN order_apply as b on a.order_id = b.id ${ew.customSqlSegment} ")
    @Results({
            @Result(column = "id",property = "id"),
            @Result(
                    property = "course",
                    column = "course_id",
                    javaType = Course.class,
                    one = @One(select = "com.xmy.cultivate.mapper.CourseMapper.selectById")
            ),
            @Result(
                    property = "organizationModel",
                    column = "school_id",
                    javaType = Organization.class,
                    one = @One(select = "com.xmy.cultivate.mapper.OrganizationMapper.selectById")
            )
    })
    public abstract List<OrderDetail> GetLessonByStuInfoId(@Param(Constants.WRAPPER) Wrapper wrapper);

    /**
     * 获取报读科目
     * @param wrapper
     * @return
     */
    @Select("select a.subjects_id as id,a.course_id,b.`name`,a.school_id from order_detail as a LEFT JOIN subjects as b on a.subjects_id = b.id ${ew.customSqlSegment} ")
    public abstract List<Subjects> GetSubjectsByStuInfoId(@Param(Constants.WRAPPER) Wrapper wrapper);



    /**
     * 报读通过课程，获取在读信息
     * @param courseId
     * @param studentId
     * @return
     */
    @Select("select a.* from order_detail as a LEFT JOIN order_apply as b on a.order_id = b.id where b.`status` <> 2 and a.deleted=0 and a.residue_course_count > 0 and a.student_id = #{studentId} and a.course_id = #{courseId} and a.school_id=#{schoolId}")
    @Results({
            @Result(column = "id",property = "id"),
            @Result(
                    property = "orderCoursePricingModel",
                    column = "course_price_id",
                    one = @One(select = "com.xmy.cultivate.mapper.CoursePricingMapper.selectById")
            )
    })
    public abstract List<OrderDetail> getLessonByCourseId(@Param("courseId") Long courseId,@Param("studentId") Long studentId,@Param("schoolId") Long schoolId);

    /**
     * 报读通过科目，获取在读信息
     * @param subjectsId
     * @param studentId
     * @return
     */
    @Select("select a.* from order_detail as a LEFT JOIN order_apply as b on a.order_id = b.id where b.`status` <> 2 and a.deleted=0 and a.residue_course_count > 0 and a.student_id = #{studentId} and a.subjects_id = #{subjectsId} and a.school_id=#{schoolId} and a.lesson_type=${lessonType}")
    /*@Results({
            @Result(column = "id",property = "id"),
            @Result(
                    property = "orderCoursePricingModel",
                    column = "id",
                    one = @One(select = "com.xmy.cultivate.mapper.OrderCoursePricingMapper.selectById")
            )
    })*/
    public abstract List<OrderDetail> getLessonForSubjects(@Param("subjectsId") Long subjectsId,@Param("studentId") Long studentId,@Param("schoolId") Long schoolId,@Param("lessonType") Integer lessonType);


    /**
     * 获取报读校区
     * @param studentId
     * @return
     */
    @Select("select b.id,b.name from order_detail as a LEFT JOIN organization as b on a.school_id = b.id where a.student_id = #{studentId} and a.status <> 2 GROUP BY a.school_id")
    public abstract List<Organization> getSignUpSchoolList(@Param("studentId") Long studentId);

    /**
     * 获取报读校区
     * @param
     * @return
     */
    @Select("select b.id,b.name from order_detail as a LEFT JOIN organization as b on a.school_id = b.id ${ew.customSqlSegment}")
    public abstract List<Organization> getSignUpSchoolListForWhere(@Param(Constants.WRAPPER) Wrapper wrapper);


    /**
     * 获取对应课程最早的报读信息，记上课，扣课时用
     * @param gradeId 班级id
     * @param count 此次扣多少个课时
     * @return
     */
    @Select({"<script>",
                "select a.id,a.order_detail_id,a.student_id,a.grade_id,a.use_course_count,a.year_part,",
                "b.residue_course_count,b.residue_price,b.all_course_count,b.serve_price,b.materials_price",
                "from student_grade as a LEFT JOIN order_detail as b on a.order_detail_id = b.id",
                "where a.deleted = 0 and a.`status` = 1 and a.reading_status = 1 and b.reading_status = 1 and b.`status` &lt;&gt;2 and b.residue_course_count &gt;= #{count} and a.student_id = #{studentId} and a.grade_id=#{gradeId}",
                "ORDER BY b.id DESC limit 0,1",
            "</script>"})
    public abstract FirstSignUp getFirstSignUp(@Param("studentId") Long studentId,@Param("gradeId") Long gradeId,@Param("count") Integer count);

    @Select("select ifnull(sum(use_course_count),0) as use_course_count from order_detail where order_id = #{orderId}")
    public abstract Integer getUseHouseForOrderId(@Param("orderId") Long orderId);


    @Select({"<script>",
            "WITH with_tb01 AS (",
            "SELECT *,",
            "order_id AS order_id_param ",
            "FROM ",
            "income_day_report_view ",
            " ${ew.customSqlSegment}",
            ")",
            "SELECT * FROM (",
            "SELECT t1.*,",
            "getStaffNameForId ( payee_id ) AS payee_name,",
            "(",
            "SELECT ",
            "CASE",
            "WHEN SUM( all_course_count ) &gt;= 45 AND SUM( all_course_count ) &lt; 90 THEN 1 ",
            "WHEN SUM( all_course_count ) &gt;= 90 THEN 2 ELSE 0 ",
            "END",
            "FROM",
            "with_tb01",
            "WHERE",
            "student_id = t1.student_id ",
            "AND subjects_id = t1.subjects_id ",
            "GROUP BY",
            "subjects_id ",
            ") AS pay_num",
            "FROM with_tb01 AS t1",
            "LEFT JOIN enroll_info AS t2 ON t1.order_id_param = t2.order_id ",
            "WHERE 1=1 ",
            "<when test='readType==1'>",
                "AND t2.type IS NOT NULL",
            "</when>",
            "<when test='readType==2'>",
                "AND t2.type IS NULL",
            "</when>",
            "GROUP BY order_id_param ",
            ") AS t01",
            "<when test='payNum!=null and payNum!=&apos;&apos; '>",
                "WHERE pay_num = ${payNum} ",
            "</when>",
            "</script>"})
    @Results({
            @Result(column = "id",property = "id"),
            @Result(
                    property = "affirmTypeList",
                    column = "order_id_param",
                    many = @Many(select = "com.xmy.cultivate.mapper.OrderApplyAffirmMapper.getAffirmTypeListForOrderId")
            )
    })
    public abstract IPage<IncomeDayReport> getIncomeDayReport(IPage<?> page, @Param(Constants.WRAPPER) Wrapper wrapper, @Param("readType") Integer readType, @Param("payNum") String payNum);

    @Select({"<script>",
            "SELECT",
                    "t1.id,",
                    "t1.add_date AS discounts_date,",
                    "t1.dis_price AS discounts_price,",
                    "t1.`status` AS `status`,",
                    "t2.`receipt_no`,",
                    "t1.`order_id`,",
                    "t1.`order_id` as order_id_param,",
                    "t2.`student_id`,",
                    "t3.`subjects_id`,",
                    "getSubjectsName ( t3.subjects_id ) AS `subjects_name`,",
                    "t4.`name` AS `student_name`,",
                    "t2.`handle_person_id`,",
                    "t2.`handle_person_name`,",
                    "t2.`handle_date`,",
                    "t2.`price`,",
                    "t2.`solid_price`,",
                    "t2.`own_price` AS `own_price`,",
                    "t2.`is_own` AS `is_own`,",
                    "t3.`school_id` AS `school_id`,",
                    "t3.`course_id` AS `course_id`,",
                    "t3.`course_name` AS `course_name`,",
                    "t3.`course_price_name` AS `course_price_name`,",
                    "t3.`arrive_affirm` AS `arrive_affirm`,",
                    "t3.`created_at` AS `created_at`,",
                    "t2.`deleted` AS `deleted`,",
                    "t2.`principal_date` AS `principal_date`,",
                    "t2.`finance_date` AS `finance_date`,",
                    "t2.`order_type` AS `order_type`,",
                    "t3.`lesson_type` AS `lesson_type`,",
                    "t3.`year_class_id` AS `year_class_id`,",
                    "t2.`is_print` AS `is_print`,",
                    "t2.`payee_id` AS `payee_id`,",
                    "`getSchoolName` ( t3.`school_id` ) AS `school_name`, ",
                    "t7.teacher_id,",
                    "getStaffNameForId(t7.teacher_id) AS teacher_name",
                    "FROM",
                    "order_dis_price AS t1",
                    "INNER JOIN order_apply AS t2 ON t1.order_id = t2.id",
                    "INNER JOIN order_detail AS t3 ON t2.id = t3.order_id",
                    "INNER JOIN student AS t4 ON t3.student_id = t4.id",
                    "LEFT JOIN enroll_info AS t5 ON t5.order_id = t1.`order_id` AND t5.type = 1",
                    "LEFT JOIN student_grade AS t6 ON t6.student_id = t3.student_id ",
                    "AND t6.year_part = t3.year_part ",
                    "AND t6.quarter_num = t3.quarter_num ",
                    "AND t6.subjects_id = t3.subjects_id ",
                    "AND t6.is_last = 1 ",
                    "AND t6.lesson_type = 1 ",
                    "LEFT JOIN grade AS t7 ON t6.grade_id = t7.id",
                    "where t2.deleted =0 and t2.status &lt;&gt;2",
                    "<when test='(startDate!=null and startDate!=&apos;&apos;) and (endDate!=null and endDate!=&apos;&apos;) '>",
                        "and t1.add_date BETWEEN #{startDate} and #{endDate}",
                    "</when>",
                    "<when test='yearPart!=null and yearPart!=&apos;&apos; '>",
                    "  and t2.year_part = ${yearPart}",
                    "</when>",
                    "<when test='quarterNum!=null and quarterNum!=&apos;&apos; '>",
                    "  and t2.quarter_num = ${quarterNum}",
                    "</when>",
                    "<when test='subjectsId!=null and subjectsId!=&apos;&apos; '>",
                    "  and t3.subjects_id in(${subjectsId})",
                    "</when>",
                    "<when test='status!=null and status!=&apos;&apos; '>",
                    "  and t1.status = ${status}",
                    "</when>",
                    "<when test='lessonType!=null and lessonType!=&apos;&apos; '>",
                    "  and t3.lesson_type = ${lessonType}",
                    "</when>",
                    "<when test='handlePersonId!=null and handlePersonId!=&apos;&apos; '>",
                    "  and t2.`handle_person_id` = ${handlePersonId}",
                    "</when>",
                    "<when test='payeeId!=null and payeeId!=&apos;&apos; '>",
                    "  and t2.`payee_id` = ${payeeId}",
                    "</when>",
                    "<when test='studentName!=null and studentName!=&apos;&apos; '>",
                    "  and t4.`name` like '%${studentName}%'",
                    "</when>",
                    "<when test='inviteId!=null and inviteId!=&apos;&apos; '>",
                    "  and t5.teacher_id IN (${inviteId}) ",
                    "</when>",
                    "<when test='teacherId!=null and teacherId!=&apos;&apos; '>",
                        "and t7.teacher_id = ${teacherId} ",
                    "</when>",
                    "<when test ='schoolIdList != null and schoolIdList.size() > 0'>",
                        "and t2.school_id in",
                        "<foreach collection='schoolIdList' item='item' open='(' separator=',' close=')'>",
                        "${item}",
                        "</foreach>",
                    "</when>",
                    "<when test ='yearClassIdList != null and yearClassIdList.size() > 0'>",
                        "and t3.year_class_id in",
                        "<foreach collection='yearClassIdList' item='item' open='(' separator=',' close=')'>",
                        "${item}",
                        "</foreach>",
                    "</when>",
                    "GROUP BY t1.id ",
                    "ORDER BY t1.id desc",
            "</script>"})
            @Results({
                    @Result(column = "id",property = "id"),
                    @Result(
                            property = "affirmTypeList",
                            column = "order_id_param",
                            many = @Many(select = "com.xmy.cultivate.mapper.OrderApplyAffirmMapper.getAffirmTypeListForOrderId")
                    )
            })
    public abstract IPage<IncomeDayReport> getDisList(IPage<?> page,
                                                      @Param("yearPart") Integer yearPart,
                                                      @Param("quarterNum") Integer quarterNum,
                                                      @Param("schoolIdList") List<Long> schoolIdList,
                                                      @Param("subjectsId") String subjectsId,
                                                      @Param("status") String status,
                                                      @Param("studentName") String studentName,
                                                      @Param("lessonType") String lessonType,
                                                      @Param("yearClassIdList") List<Integer> yearClassIdList,
                                                      @Param("handlePersonId") String handlePersonId,
                                                      @Param("startDate") String startDate,
                                                      @Param("endDate") String endDate,
                                                      @Param("orderType") String orderType,
                                                      @Param("payeeId") String payeeId,
                                                      @Param("inviteId") String inviteId,
                                                      @Param("teacherId") String teacherId
    );

    /**
     * 获取优惠合计
     * @param
     * @return
     */
    @Select({"<script>",
            "SELECT",
            "ifnull(sum(dis_price),0) as dis_price",
            "FROM (",
                    "SELECT",
                    "t1.dis_price ",
                    "FROM",
                    "order_dis_price AS t1",
                    "INNER JOIN order_apply AS t2 ON t1.order_id = t2.id",
                    "INNER JOIN order_detail AS t3 ON t2.id = t3.order_id",
                    "INNER JOIN student AS t4 ON t3.student_id = t4.id",
                    "LEFT JOIN enroll_info AS t5 ON t5.order_id = t1.`order_id` AND t5.type = 1",
                    "LEFT JOIN student_grade AS t6 ON t6.student_id = t3.student_id ",
                    "AND t6.year_part = t3.year_part ",
                    "AND t6.quarter_num = t3.quarter_num ",
                    "AND t6.subjects_id = t3.subjects_id ",
                    "AND t6.is_last = 1 ",
                    "AND t6.lesson_type = 1",
                    "LEFT JOIN grade AS t7 ON t6.grade_id = t7.id",
                    "where t2.deleted =0 and t2.status &lt;&gt;2",
                    "<when test='(startDate!=null and startDate!=&apos;&apos;) and (endDate!=null and endDate!=&apos;&apos;) '>",
                        "and t1.add_date BETWEEN #{startDate} and #{endDate}",
                    "</when>",
                    "<when test='yearPart!=null and yearPart!=&apos;&apos; '>",
                    "  and t2.year_part = ${yearPart}",
                    "</when>",
                    "<when test='quarterNum!=null and quarterNum!=&apos;&apos; '>",
                    "  and t2.quarter_num = ${quarterNum}",
                    "</when>",
                    "<when test='subjectsId!=null and subjectsId!=&apos;&apos; '>",
                    "  and t3.subjects_id in(${subjectsId})",
                    "</when>",
                    "<when test='status!=null and status!=&apos;&apos; '>",
                    "  and t1.status = ${status}",
                    "</when>",
                    "<when test='lessonType!=null and lessonType!=&apos;&apos; '>",
                    "  and t3.lesson_type = ${lessonType}",
                    "</when>",
                    "<when test='handlePersonId!=null and handlePersonId!=&apos;&apos; '>",
                    "  and t2.`handle_person_id` = ${handlePersonId}",
                    "</when>",
                    "<when test='payeeId!=null and payeeId!=&apos;&apos; '>",
                    "and t2.`payee_id` = ${payeeId}",
                    "</when>",
                    "<when test='studentName!=null and studentName!=&apos;&apos; '>",
                    "  and t4.`name` like '%${studentName}%'",
                    "</when>",
                    "<when test='inviteId!=null and inviteId!=&apos;&apos; '>",
                        "  and t5.teacher_id IN (${inviteId}) ",
                    "</when>",
                    "<when test='teacherId!=null and teacherId!=&apos;&apos; '>",
                        "and t7.teacher_id = ${teacherId} ",
                    "</when>",
                    "<when test ='schoolIdList != null and schoolIdList.size() > 0'>",
                        "and t2.school_id in",
                        "<foreach collection='schoolIdList' item='item' open='(' separator=',' close=')'>",
                        "${item}",
                        "</foreach>",
                    "</when>",
                    "<when test ='yearClassIdList != null and yearClassIdList.size() > 0'>",
                        "and t3.year_class_id in",
                        "<foreach collection='yearClassIdList' item='item' open='(' separator=',' close=')'>",
                        "${item}",
                        "</foreach>",
                    "</when>",
            "GROUP BY t1.id ",
            ") AS t ",
            "</script>"})
    public abstract BigDecimal getDisPrice(
                                                      @Param("yearPart") Integer yearPart,
                                                      @Param("quarterNum") Integer quarterNum,
                                                      @Param("schoolIdList") List<Long> schoolIdList,
                                                      @Param("subjectsId") String subjectsId,
                                                      @Param("status") String status,
                                                      @Param("studentName") String studentName,
                                                      @Param("lessonType") String lessonType,
                                                      @Param("yearClassIdList") List<Integer> yearClassIdList,
                                                      @Param("handlePersonId") String handlePersonId,
                                                      @Param("startDate") String startDate,
                                                      @Param("endDate") String endDate,
                                                      @Param("orderType") String orderType,
                                                      @Param("payeeId") String payeeId,
                                                      @Param("inviteId") String inviteId,
                                                      @Param("teacherId") String teacherId
                                                      );

    @Select("select ifnull(sum(solid_price),0) as solid_price from income_day_report_view ${ew.customSqlSegment}")
    public abstract BigDecimal getSolidStatisticsPrice(@Param(Constants.WRAPPER) Wrapper wrapper);

    /**
     * 获取应收金额合计
     * @param wrapper
     * @return
     */
    @Select("select ifnull(sum(price),0) as price from income_day_report_view ${ew.customSqlSegment}")
    public abstract BigDecimal getStatisticsPrice(@Param(Constants.WRAPPER) Wrapper wrapper);

    /**
     * 获取所有课时
     * @param schoolId
     * @param studentId
     * @param subjects_id
     * @return
     */
    @Select("select (sum(all_course_count) - sum(out_house)) as all_count from (select all_course_count,(select IFNULL(sum(out_house),0) from order_transfer_detail as b where b.old_order_detail_id = a.id and b.status<>2) as out_house from order_detail as a where a.deleted = 0 and a.lesson_type =${lessonType} and a.`status`<>2 and a.student_id = #{studentId} and a.school_id=#{schoolId} and subjects_id=#{subjectsId} and year_part = #{yearPart} and `quarter_num` = #{quarterNum}) as t")
    public abstract Integer getAllCouseHouse(@Param("schoolId") Long schoolId,@Param("studentId") Long studentId,@Param("subjectsId") Long subjects_id,@Param("yearPart") Integer yearPart,@Param("quarterNum") Integer quarterNum,@Param("lessonType") Integer lessonType);

    /**
     * 获取所有实收金额
     * @return
     */
    @Select("select sum(solid_price) as allSolidPrice from order_detail as a where a.deleted = 0 and a.`status`<>2 and order_type=1 and a.student_id = #{studentId} and a.school_id=#{schoolId} and subjects_id=#{subjectsId} and lesson_type=${lessonType}")
    public abstract BigDecimal getAllSolidPrice(@Param("schoolId") Long schoolId,@Param("studentId") Long studentId,@Param("subjectsId") Long subjects_id,@Param("lessonType") Integer lessonType);

    @Select({"<script>",
            "SELECT",
            "school_id,subjects_id,year_class_id,lesson_type,student_id,IFNULL(SUM(residue_course_count),0) as residue_course_count",
            "FROM",
            "order_detail",
            "WHERE",
            "year_part = ${yearPart} ",
            "AND quarter_num = ${quarterNum} ",
            "AND `status` &lt;&gt; 2 ",
            "AND deleted = 0",
            "AND residue_course_count&gt;0",
            "AND lesson_type = ${lessonType}",
            "<when test='schoolId!=null'>",
            "AND school_id = ${schoolId}",
            "</when>",
            "<when test='subjectsId!=null'>",
            "AND subjects_id = ${subjectsId}",
            "</when>",
            "<when test='studentId!=null'>",
            "AND student_id = ${studentId}",
            "</when>",
            "GROUP BY school_id,student_id,subjects_id,lesson_type",
            "ORDER BY school_id,subjects_id,lesson_type,student_id",
            "</script>"})
    public abstract List<OrderDetail> getStudentOrderDetail(@Param("yearPart") Integer yearPart,@Param("quarterNum") Integer quarterNum,@Param("schoolId") Long schoolId,@Param("subjectsId") Long subjectsId,@Param("lessonType") Integer lessonType,@Param("studentId") Long studentId);

    @Select({"<script>",
            "SELECT",
            "*",
            "FROM",
            "order_detail",
            "WHERE",
            "year_part = ${yearPart} ",
            "AND quarter_num = ${quarterNum} ",
            "AND `status` &lt;&gt; 2 ",
            "AND deleted = 0",
            "AND lesson_type = ${lessonType}",
            "<when test='schoolId!=null'>",
            "AND school_id = ${schoolId}",
            "</when>",
            "<when test='subjectsId!=null'>",
            "AND subjects_id = ${subjectsId}",
            "</when>",
            "<when test='studentId!=null'>",
            "AND student_id = ${studentId}",
            "</when>",
            "limit 1",
            "</script>"})
    public abstract OrderDetail getStudetnOrderDetailForLessonType(@Param("yearPart") Integer yearPart,@Param("quarterNum") Integer quarterNum,@Param("schoolId") Long schoolId,@Param("subjectsId") Long subjectsId,@Param("lessonType") Integer lessonType,@Param("studentId") Long studentId);

    /**
     * 财务报表
     * @param page
     * @param schoolId
     * @param subjectsId
     * @return
     */
    @Select({"<script>",
            "select",
            "a.student_id,a.student_name,a.grade_id,a.year_class_id,a.subjects_id,deleted,status,order_type,year_and_quarter,id,",
            "SUM(a.all_course_count-a.roll_out_house) as all_course_count,",
            "SUM(a.use_course_count) as use_course_count,",
            "SUM(a.residue_course_count) as residue_course_count,",
            "SUM(a.price-a.roll_out_price) as price,",
            "SUM(a.discounts_price) as discounts_price,",
            "SUM(residue_price) as residue_price,",
            "SUM(a.out_price + a.turn_out_price) as out_price,",
            "SUM(a.solid_price-a.roll_out_price) as solid_price,",
            "SUM(a.use_price+a.abnormal_price) as reality_pirce,",
            "sum(a.abnormal_price) as abnormal_price, ",
            "<if test= 'type == 1'>",
                "( ",
                "SELECT ",
                "SUM( CASE reading_status WHEN 4 THEN 1 ELSE 0 END ) ",
                "FROM ",
                "student_grade AS sg ",
                "WHERE ",
                "sg.student_id = a.student_id ",
                "AND sg.year_part = a.year_part ",
                "AND sg.quarter_num = a.quarter_num ",
                "AND sg.school_id = a.school_id ",
                "AND sg.subjects_id = a.subjects_id ",
                "AND sg.lesson_type = a.lesson_type ",
                "AND sg.`status` &lt;&gt; 3 ",
                "AND sg.deleted = 0 ",
                ") AS stop_status_count, ",
            "</if>",
            "<if test= 'type == 3'>",
                "( ",
                "SELECT ",
                "SUM( CASE reading_status WHEN 1 THEN 1 ELSE 0 END )  ",
                "FROM ",
                "student_grade AS sg ",
                "WHERE ",
                "sg.student_id = a.student_id ",
                "AND sg.year_part = a.year_part ",
                "AND sg.quarter_num = a.quarter_num ",
                "AND sg.school_id = a.school_id ",
                "AND sg.subjects_id = a.subjects_id ",
                "AND sg.lesson_type = a.lesson_type ",
                "AND sg.`status` &lt;&gt; 3 ",
                "AND sg.deleted = 0 ",
                ") AS stop_status_count, ",
            "</if>",
            "<if test= 'type == 3 || type == 4'>",
            "SUM( CASE WHEN a.out_house > 0 THEN 1 ELSE 0 END ) AS is_out, ",
            "SUM( CASE WHEN a.turn_out_house > 0 THEN 1 ELSE 0 END ) AS is_turn_out, ",
            "</if>",
            "school_id ",
            "<when test='history == 0'>",
                "from order_detail_statement_view as a ",
            "</when>",
            "<when test='history == 1'>",
                "from order_detail_history_statement_view as a ",
            "</when>",
            "where",
            "deleted = 0 and `status`&lt;&gt;2",
            "and order_type in(1,3,5,8,9)",
            "<when test='schoolId!=null and schoolId!=&apos;&apos;'>",
            "and school_id=${schoolId}",
            "</when>",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos;'>",
            "and subjects_id=${subjectsId} ",
            "</when>",
            "AND year_part = ${yearPart} ",
            "AND quarter_num = ${quarterNum} ",
            "<when test='startDate!=null and startDate!=&apos;&apos;'>",
            "AND handle_date &gt;= '${startDate}' ",
            "</when>",
            "<when test='endDate!=null and endDate!=&apos;&apos;'>",
            "AND handle_date &lt;= '${endDate}' ",
            "</when>",
            "<when test='studentName!=null and studentName!=&apos;&apos;'>",
            "and student_name like &apos;%${studentName}%&apos;",
            "</when>",
            "<when test='lessonType!=null and lessonType!=&apos;&apos;'>",
            "and lesson_type = ${lessonType}",
            "</when>",
            "GROUP BY",
            "school_id",
            ",subjects_id",
            ",student_id ",
            "HAVING 1 = 1 ",
            "<if test= 'type == 1'>",
            "AND use_course_count > 0 ",
            "AND residue_course_count &gt; 0 ",
            "AND stop_status_count &gt; 0 ",
            "</if>",
            "<if test= 'type == 3'>",
            "AND residue_course_count = 0 ",
            "AND is_out > 0 ",
            "AND is_turn_out = 0 ",
            "</if>",
            "<if test= 'type == 4'>",
            "AND is_turn_out &gt; 0 ",
            "AND residue_course_count = 0 ",
            "AND is_out = 0 ",
            "</if>",
            "<if test= 'type == 5'>",
            "AND use_course_count = 0 ",
            "AND residue_course_count &gt; 0 ",
            "</if>",
            "</script>"})
    public abstract IPage <StatementData> statementData (IPage <?> page,@Param ("schoolId") String schoolId,@Param ("subjectsId") String subjectsId,@Param ("studentName") String studentName,@Param ("yearPart") String yearPart,@Param ("quarterNum") String quarterNum,@Param ("lessonType") String lessonType,@Param ("history") Integer history,@Param ("type") Integer type,@Param ("startDate") String startDate,@Param ("endDate") String endDate );

    @Select(
            {"<script>",
                    "SELECT year_part,quarter_num,handle_date,t3.school_id,subjects_id,student_id,use_course_count,residue_course_count,",
                    "student.`name` AS student_name,",
                    "t3.year_class_id, ",
                    "IFNULL( stop_status_count, 0 ) AS stop_status_count,",
                    "IFNULL( reading_status_count, 0 ) AS reading_status_count,",
                    "is_out,is_turn_out,solid_price,reality_pirce,abnormal_price ,",
                    "all_course_count,price,discounts_price,residue_price,out_price ",
                    "FROM ( ",
                    "SELECT year_part,quarter_num,school_id,subjects_id,student_id,handle_date,year_class_id,",
                    "SUM( use_course_count ) AS use_course_count,",
                    "SUM( residue_course_count ) AS residue_course_count,",
                    "SUM( CASE WHEN t1.out_house &gt; 0 THEN 1 ELSE 0 END ) AS is_out,",
                    "SUM( CASE WHEN t1.turn_out_house &gt; 0 THEN 1 ELSE 0 END ) AS is_turn_out,",
                    "( ",
                    "SELECT ",
                    "SUM( CASE reading_status WHEN 4 THEN 1 ELSE 0 END ) AS stop_status_count ",
                    "FROM ",
                    "student_grade AS t2 ",
                    "WHERE ",
                    "t1.year_part = t2.year_part ",
                    "AND t1.quarter_num = t2.quarter_num ",
                    "AND t1.school_id = t2.school_id ",
                    "AND t1.subjects_id = t2.subjects_id ",
                    "AND t1.student_id = t2.student_id ",
                    "AND t1.lesson_type = t2.lesson_type ",
                    "AND t2.`status` &lt;&gt; 3 ",
                    "AND t2.deleted = 0 ",
                    "ORDER BY id DESC ",
                    "LIMIT 1 ",
                    ") AS stop_status_count,",
                    "( ",
                    "SELECT ",
                    "SUM( CASE reading_status WHEN 1 THEN 1 ELSE 0 END ) AS reading_status_count ",
                    "FROM ",
                    "student_grade AS t2 ",
                    "WHERE ",
                    "t1.year_part = t2.year_part ",
                    "AND t1.quarter_num = t2.quarter_num ",
                    "AND t1.school_id = t2.school_id ",
                    "AND t1.subjects_id = t2.subjects_id ",
                    "AND t1.student_id = t2.student_id ",
                    "AND t1.lesson_type = t2.lesson_type ",
                    "AND t2.`status` &lt;&gt; 3 ",
                    "AND t2.deleted = 0 ",
                    "ORDER BY id DESC ",
                    "LIMIT 1 ",
                    ") AS reading_status_count,",
                    "SUM( t1.all_course_count - t1.roll_out_house ) AS all_course_count,",
                    "SUM( t1.price - t1.roll_out_price ) AS price,",
                    "SUM( t1.discounts_price ) AS discounts_price,",
                    "SUM( residue_price ) AS residue_price,",
                    "SUM( t1.out_price + t1.turn_out_price ) AS out_price,",
                    "SUM( t1.solid_price - t1.roll_out_price ) AS solid_price,",
                    "SUM( t1.use_price + t1.abnormal_price ) AS reality_pirce,",
                    "SUM( t1.abnormal_price ) AS abnormal_price ",
                    "FROM ",
                    "<when test='history == 0'>",
                    " order_detail AS t1 ",
                    "</when>",
                    "<when test='history == 1'>",
                    " order_detail_history AS t1 ",
                    "</when>",
                    "WHERE ",
                    "year_part = ${yearPart} ",
                    "AND quarter_num = ${quarterNum} ",
                    "AND `status` &lt;&gt; 2 ",
                    "AND deleted = 0 ",
                    "AND lesson_type = 1 ",
                    "GROUP BY ",
                    "year_part,quarter_num,school_id,subjects_id,student_id ",
                    ") AS t3 ",
                    "LEFT JOIN student ON student.id = student_id ",
                    "GROUP BY ",
                    "school_id,subjects_id,student_id ",
                    "HAVING ",
                    "use_course_count &gt; 0 ",
                    "AND residue_course_count &gt; 0 ",
                    "AND reading_status_count &gt; 0 ",
                    "AND stop_status_count = 0 ",
                    "<when test='schoolId!=null and schoolId!=&apos;&apos;'>",
                    "AND `school_id` = ${schoolId} ",
                    "</when>",
                    "<when test='startDate!=null and startDate!=&apos;&apos;'>",
                    "AND handle_date &gt;= '${startDate}' ",
                    "</when>",
                    "<when test='endDate!=null and endDate!=&apos;&apos;'>",
                    "AND handle_date &lt;= '${endDate}' ",
                    "</when>",
                    "<when test='studentName!=null and studentName!=&apos;&apos;'>",
                    "AND student_name like &apos;%${studentName}%&apos; ",
                    "</when>",
                    "<when test='subjectsId!=null and subjectsId!=&apos;&apos;'>",
                    "AND `subjects_id` =  ${subjectsId} ",
                    "</when>",
            "</script>"})
    public abstract IPage <StatementData> statementDataStill (IPage <?> page,@Param ("schoolId") String schoolId,@Param ("subjectsId") String subjectsId,@Param ("studentName") String studentName,@Param ("yearPart") String yearPart,@Param ("quarterNum") String quarterNum,@Param ("lessonType") String lessonType,@Param ("history") Integer history,@Param ("startDate") String startDate,@Param ("endDate") String endDate );

    @Select({"<script>",
            "SELECT ",
            "a.student_id, ",
            "a.student_name, ",
            "a.year_class_id, ",
            "a.subjects_id, ",
            "SUM( a.all_course_count - a.roll_out_house ) AS all_course_count,",
            "SUM( a.use_course_count ) AS use_course_count,",
            "SUM( a.residue_course_count ) AS residue_course_count,",
            "SUM( a.price - a.roll_out_price ) AS price,",
            "SUM( a.discounts_price ) AS discounts_price,",
            "SUM( residue_price ) AS residue_price,",
            "SUM( a.out_price + a.turn_out_price ) AS out_price,",
            "SUM( a.solid_price - a.roll_out_price ) AS solid_price,",
            "SUM( a.use_price + a.abnormal_price ) AS reality_pirce ,",
            "SUM( a.abnormal_price ) AS abnormal_price,",
            "school_id ",
            "FROM ",
            "<when test='history == 0'>",
            " order_detail_statement_view as a ",
            "</when>",
            "<when test='history == 1'>",
            " order_detail_history_statement_view as a ",
            "</when>",
            "WHERE ",
            "deleted = 0 ",
            "AND `status` &lt;&gt; 2 ",
            "AND order_type IN ( 1, 3, 5,8,9 ) ",
            "<when test='schoolId!=null and schoolId!=&apos;&apos;'>",
            "AND a.school_id = ${schoolId} ",
            "</when>",
            "<when test='studentName!=null and studentName!=&apos;&apos;'>",
            "AND student_name like &apos;%${studentName}%&apos; ",
            "</when>",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos;'>",
            "AND `subjects_id` = ${subjectsId} ",
            "</when>",
            "<when test='startDate!=null and startDate!=&apos;&apos;'>",
            "AND handle_date &gt;= '${startDate}' ",
            "</when>",
            "<when test='endDate!=null and endDate!=&apos;&apos;'>",
            "AND handle_date &lt;= '${endDate}' ",
            "</when>",
            "AND year_part = ${yearPart} ",
            "AND quarter_num = ${quarterNum} ",
            "AND lesson_type = 1 ",
            "AND student_id IN ( ",
            "SELECT student_id ",
            "FROM ( ",
            "SELECT ",
            "id,year_part,quarter_num,school_id,subjects_id,student_id,use_course_count,residue_course_count, ",
            "IFNULL( stop_status_count, 0 ) AS stop_status_count, ",
            "IFNULL( reading_status_count, 0 ) AS reading_status_count, ",
            "is_out,is_turn_out ",
            "FROM ( ",
            "SELECT ",
            "id,year_part,quarter_num,school_id,subjects_id,student_id, ",
            "SUM( use_course_count ) AS use_course_count,",
            "SUM( residue_course_count ) AS residue_course_count,",
            "SUM( CASE WHEN t1.out_house > 0 THEN 1 ELSE 0 END ) AS is_out,",
            "SUM( CASE WHEN t1.turn_out_house > 0 THEN 1 ELSE 0 END ) AS is_turn_out,",
            "( ",
            "SELECT ",
            "SUM( CASE reading_status WHEN 4 THEN 1 ELSE 0 END ) AS stop_status_count ",
            "FROM ",
            "student_grade AS t2 ",
            "WHERE ",
            "t1.year_part = t2.year_part ",
            "AND t1.quarter_num = t2.quarter_num ",
            "AND t1.school_id = t2.school_id ",
            "AND t1.subjects_id = t2.subjects_id ",
            "AND t1.student_id = t2.student_id ",
            "AND t1.lesson_type = t2.lesson_type ",
            "AND t2.`status` &lt;&gt; 3 ",
            "AND t2.deleted = 0 ",
            "ORDER BY ",
            "id DESC ",
            "LIMIT 1 ",
            ") AS stop_status_count,",
            "( ",
            "SELECT ",
            "SUM( CASE reading_status WHEN 1 THEN 1 ELSE 0 END ) AS reading_status_count ",
            "FROM ",
            "student_grade AS t2 ",
            "WHERE ",
            "t1.year_part = t2.year_part ",
            "AND t1.quarter_num = t2.quarter_num ",
            "AND t1.school_id = t2.school_id ",
            "AND t1.subjects_id = t2.subjects_id ",
            "AND t1.student_id = t2.student_id ",
            "AND t1.lesson_type = t2.lesson_type ",
            "AND t2.`status` &lt;&gt; 3 ",
            "AND t2.deleted = 0 ",
            "ORDER BY id DESC ",
            "LIMIT 1 ",
            ") AS reading_status_count ",
            "FROM ",
            "order_detail AS t1 ",
            "WHERE ",
            "year_part = ${yearPart} ",
            "AND quarter_num = ${quarterNum} ",
            "AND `status` &lt;&gt; 2 ",
            "AND deleted = 0 ",
            "AND lesson_type = 1 ",
            "GROUP BY quarter_num,year_part,school_id,subjects_id,student_id ",
            ") AS t3 ",
            "GROUP BY school_id,subjects_id,student_id ",
            ") AS t4 ",
            "WHERE (( ",
            "residue_course_count = 0 ",
            "AND reading_status_count = 0 ",
            "AND stop_status_count = 0 ",
            "AND is_out = 0 ",
            "AND is_turn_out = 0 ",
            ") ",
            "OR ( residue_course_count > 0 AND use_course_count &gt; 0 AND reading_status_count = 0 AND stop_status_count = 0 )) ",
            "<when test='schoolId!=null and schoolId!=&apos;&apos;'>",
            "AND school_id = ${schoolId} ",
            "</when>",
            ") ",
            "GROUP BY student_id ",
            "</script>"})
    public abstract IPage <StatementData> statementDataEnd (IPage <?> page,@Param ("schoolId") String schoolId,@Param ("subjectsId") String subjectsId,@Param ("studentName") String studentName,@Param ("yearPart") String yearPart,@Param ("quarterNum") String quarterNum,@Param ("lessonType") String lessonType,@Param ("history") Integer history,@Param ("startDate") String startDate,@Param ("endDate") String endDate );


    /**
     * 获取退费金额
     * @param wrapper
     * @return
     */
    @Select("select IFNULL(SUM(a.out_price),0) as out_price from order_transfer_detail as a LEFT JOIN order_detail as b on a.old_order_detail_id = b.id ${ew.customSqlSegment}")
    public abstract BigDecimal getStudentOutPrice(@Param(Constants.WRAPPER) Wrapper wrapper);

    /**
     * 获取学生使用金额
     * @param wrapper
     * @return
     */
    @Select("select a.student_id,a.school_id,a.subjects_id,SUM(b.buckle_class_house) as buckle_class_house,SUM(b.buckle_class_money) as buckle_class_money,SUM(b.serve_price) as serve_price from record_course as a LEFT JOIN record_cost as b on a.id = b.id ${ew.customSqlSegment}")
    public abstract BigDecimal getStudentUsePrice(@Param(Constants.WRAPPER) Wrapper wrapper);


    /**
     * 交费率使用
     * 获取课时人数
     * @return
     */
    @Select("select count(*) from (select * from (select sum(residue_course_count) as residue_course_count,school_id,student_id from order_detail ${ew.customSqlSegment}) as t where 1=1 ${where02}) as tc")
    public abstract Integer getCourseCount(@Param(Constants.WRAPPER) Wrapper wrapper,@Param("where02") String where02);

    /**
     * 交费率使用(获取历史数据)
     * 获取课时人数
     * @return
     */
    @Select("select count(*) from (select * from (select sum(residue_course_count) as residue_course_count,school_id,student_id from order_detail_history ${ew.customSqlSegment}) as t where 1=1 ${where02}) as tc")
    public abstract Integer getCourseCountHistory(@Param(Constants.WRAPPER) Wrapper wrapper,@Param("where02") String where02);



    @Select("select sum(discounts_price) as discounts_price from order_detail where order_id = ${orderId}")
    public abstract BigDecimal getOrderDiscounts(@Param("orderId") Long orderId);

    /**
     * 获取年级
     * @param orderId
     * @return
     */
    @Select("select getDictName(year_class_id, 14) as yearClassName from order_detail where order_id = ${orderId} limit 1")
    public abstract String getYearClassNameForOrderId(@Param("orderId") Long orderId);

    /**
     * 获取课程名
     * @param orderId
     * @return
     */
    @Select("select getCourseNameForId(course_id) as course_name from order_detail where order_id =  ${orderId}  GROUP BY course_id")
    public abstract List<String> getCourseNameListForOrderId(@Param("orderId") Long orderId);


    /**
     * 临时获取寒假大于15课次的数据，以后可以删除掉
     * @return
     */
    @Select("select * from (select SUM(all_course_count-roll_out_house) as all_course_count,student_id,subjects_id,year_part,quarter_num,created_at,id from order_detail where year_part = 2023 and quarter_num = 1 and lesson_type=1 and deleted = 0 and status<>2 and out_house = 0 and order_type in(1,3) GROUP BY student_id,subjects_id,year_part,quarter_num ORDER BY created_at) as t where all_course_count>=45")
    public abstract List<OrderDetail> getBigFifteeList();


    @Select("select * from (select IFNULL(SUM(use_course_count),0) as use_course_count from (select a.use_course_count,b.teacher_id,a.year_part,a.quarter_num,b.school_id,a.subjects_id,student_id from student_grade as a LEFT JOIN grade as b on a.grade_id = b.id where student_id) as t ${ew.customSqlSegment}) as a ")
    public abstract Integer getUseCourseHouse(@Param(Constants.WRAPPER) Wrapper wrapper);

    @Select("select SUM(a.residue_course_count) as residue_course_count from order_detail as a LEFT JOIN student as b on a.student_id = b.id where a.year_and_quarter < 20232 and a.residue_course_count>0 and a.student_id = #{studentId}")
    public abstract Integer getCourseHouse(Long studentId);

    /**
     * 临时用
     * @param schoolId
     * @param studentId
     * @param subjectsId
     * @return
     */
    @Select("select sum(ifnull(residue_course_count,0)) as count,school_id,student_id from order_detail where school_id = #{schoolId} and student_id=#{studentId} and subjects_id=#{subjectsId} and deleted=0")
    public abstract Map<String,Object> getResidueCourseCountTow(@Param("schoolId")Long schoolId,@Param("studentId")Long studentId,@Param("subjectsId")Long subjectsId);

    @Select("select * from (select * from  order_detail GROUP BY school_id,student_id ) as t where t.school_id =${schoolId} and  t.student_id not in(select student_id from student_school where school_id = ${schoolId})")
    public List<OrderDetail> notAtSchoolStudent(@Param("schoolId")Long schoolId);

    @Select("select SUM(out_price) as out_price from order_detail where id in(select old_order_detail_id from order_transfer_detail where order_id = ${orderId}) and year_part=${yearPart} and quarter_num =${quarterNum}")
    public abstract BigDecimal getOutPrice(@Param("orderId") Long orderId,@Param("yearPart") Long yearPart,@Param("quarterNum") Long quarterNum);

    /**
     * 获取剩余课时大于0的订单，初始化班型用
     * @return
     */
    @Select("select school_id,student_id,subjects_id,residue_course_count_all from (select *,sum(residue_course_count) as residue_course_count_all from order_detail where deleted=0 and year_part = 2023 and quarter_num = 4 and `status`<>2  GROUP BY school_id,student_id,subjects_id) as t where residue_course_count_all>0 ORDER BY subjects_id asc")
    public abstract List<OrderDetail> getOrderDetailForResidueCoruseCount();

    @Select("SELECT count( DISTINCT student_id ) FROM order_detail ${ew.customSqlSegment}")
    public abstract Integer countStudent(@Param(Constants.WRAPPER) QueryWrapper queryWrapper);

    @Select("select * from order_detail where year_part = ${yearPart} and quarter_num = ${quarterNum} and lesson_type =${lessonType} and student_id = ${studentId} and subjects_id =${subjectsId} and deleted = 0 ORDER BY id desc limit 1")
    public abstract OrderDetail getLastOneForStudent(@Param("yearPart") Integer yearPart,@Param("quarterNum") Integer quarterNum,@Param("studentId") Long studentId,@Param("subjectsId") Long subjectsId,@Param("lessonType") Integer lessonType);


    /**
     * 人员分析 缴费率
     *
     * @return
     */
    @Select({"<script>",
            "SELECT",
            "count( id ) AS studentAllCount,",
            "SUM( CASE WHEN residue_course_count_all &gt;= 45 THEN 1 ELSE 0 END ) AS bigFifteen,",
            "SUM( CASE WHEN residue_course_count_all  &lt;45 THEN 1 ELSE 0 END ) AS littleFifteen,",
            "teacher_id,",
            "getStaffNameForId ( teacher_id ) AS teacherName,",
            "school_id,",
            "getSchoolName(school_id) as schoolName,",
            "subjects_id,",
            "getSubjectsName(subjects_id) as subjects_name,",
            "(",
            "SELECT",
            "IFNULL( sum( num ), 0 ) AS all_num",
            "FROM",
            "( SELECT sum( num ) AS num FROM rate_pay ",
            "WHERE",
            "deleted =0 and status=1",
            "and lesson_type = t3.lesson_type",
            "<when test='yearPart!=null and yearPart!=&apos;&apos;'>",
            "and rate_pay.year_part = #{yearPart}",
            "</when>",
            "<when test='quarterNum!=null and quarterNum!=&apos;&apos;'>",
            "and rate_pay.quarter_num = #{quarterNum}",
            "</when>",
            "and student_id not in(select student_id from renew_no_student where deleted=0 and year_part =${yearPart} and quarter_num = ${quarterNum})",
            "<choose>",
            "<when test='quarterNum == 2'>",
            "  and year_class_id &lt; 13",
            "</when>",
            "<otherwise>",
            "  and year_class_id &lt; 13",
            "</otherwise>",
            "</choose>",
            "and teacher_id = t3.teacher_id",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos; '>",
            "and subjects_id = t3.subjects_id",
            "</when>",
            ") AS t_rate_pay",
            ")",
            " AS payNum",
            "FROM",
            "(",
            "SELECT",
            "id,",
            "school_id,",
            "teacher_id,",
            "student_id,",
            "subjects_id,",
            "year_part,",
            "quarter_num,",
            "grade_id,",
            "lesson_type,",
            "residue_course_count_all",
            "FROM",
            "(",
            "SELECT",
            "*,(",
            "SELECT",
            "sum(residue_course_count)",
            "FROM",
            "<when test='isHistory == 0'>",
            "order_detail AS t2",
            "</when>",
            "<when test='isHistory == 1'>",
            "order_detail_history AS t2",
            "</when>",
            "WHERE",
            "t2.student_id = t1.student_id",
            "AND t2.school_id = t1.school_id",
            "AND t2.subjects_id = t1.subjects_id",
            "and t2.year_part = t1.year_part ",
            "and t2.quarter_num = t1.quarter_num",
            "and t2.lesson_type = t1.lesson_type",
            "and t2.status&lt;&gt;2 and deleted =0",
            ") AS residue_course_count_all",
            "FROM",
            "student_grade_view AS t1",
            "WHERE",
            "t1.year_part = ${yearPart}",
            "AND t1.quarter_num = ${quarterNum}",
            "and t1.is_last = 1",
            "and t1.is_makeup = 0",
            "and t1.deleted = 0",
            "and t1.status = 1",
            "and t1.lesson_type = 1",
            "and (t1.reading_status in(1,4,5,6) or (t1.reading_status=3 and t1.use_course_count&gt;0))",
            "and student_id not in(select student_id from renew_no_student where deleted=0 and year_part =${yearPart} and quarter_num = ${quarterNum})",
            "<choose>",
            "<when test='quarterNum == 2'>",
            "  and t1.year_class_id &lt; 13",
            "</when>",
            "<otherwise>",
            "  and t1.year_class_id &lt; 13",
            "</otherwise>",
            "</choose>",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos; '>",
            "  and t1.subjects_id = #{subjectsId}",
            "</when>",
            "<when test='schoolId!=null and schoolId!=&apos;&apos; '>",
            "  and t1.school_id = #{schoolId}",
            "</when>",
            "GROUP BY",
            "school_id,",
            "student_id,",
            "subjects_id,",
            "teacher_id",
            ") AS t",
            ") AS t3",
            "WHERE teacher_id IN (${teacherStr})",
            "GROUP BY teacher_id",
            "</script>"})
    public abstract List<RenewClassRecord> staffAnalysis(@Param("schoolId") String schoolId, @Param("yearPart") String yearPart, @Param("quarterNum") String quarterNum, @Param("subjectsId") String subjectsId, @Param("isHistory") Integer isHistory,@Param("teacherStr") String teacherStr);


    /**
     * 报科目人数
     * @return
     */
    @Select({"<script>",
            "SELECT COUNT(*) AS num_students,",
            "COUNT( CASE WHEN num_subjects = 1 THEN 1 END ) AS one_subject,",
            "COUNT( CASE WHEN num_subjects = 2 THEN 1 END ) AS two_subjects,",
            "COUNT( CASE WHEN num_subjects = 3 THEN 1 END ) AS three_subjects",
            "FROM ",
            "( ",
            "SELECT student_id, ",
            "COUNT( DISTINCT subjects_id ) AS num_subjects ",
            "FROM ",
            "student sd ",
            "LEFT JOIN order_detail od ON sd.id = od.student_id ",
            "WHERE ",
            "sd.deleted = 0 ",
            "AND od.deleted = 0 ",
            "AND sd.id IN ( SELECT student_id FROM enroll_info )",
            "AND sd.id IN ( SELECT student_id FROM order_apply WHERE deleted = 0 AND `status` &lt;&gt; 2 GROUP BY student_id ) ",
            "GROUP BY sd.id ",
            ") AS subquery;",
            "</script>"})
    public abstract List<OrderDetail> countStudentsBySubject();

    /**
     * 学员总人数
     * @return
     */
    @Select("SELECT COUNT(*) AS num_students FROM ( SELECT id FROM student WHERE deleted = 0 AND id IN ( SELECT student_id FROM order_apply WHERE deleted = 0 AND `status` <> 2 GROUP BY student_id ) GROUP BY id ) AS subquery")
    public abstract Integer studentCount();

    /**
     * 报科目人数
     * @return
     */
    @Select({"<script>",
            "WITH recent_orders AS ( SELECT *, ROW_NUMBER() OVER ( PARTITION BY student_id ORDER BY id ASC ) AS rn FROM order_detail WHERE lesson_type=1),",
            "previous_subjects AS ( SELECT student_id, subjects_id AS previous_subject FROM recent_orders WHERE rn = 1 ),",
            "current_subjects AS ( SELECT student_id, subjects_id AS current_subject FROM recent_orders WHERE rn = 2 ) ",
            "SELECT ",
            "previous_subject,current_subject,COUNT(*) AS transfer_count  ",
            "FROM ",
            "previous_subjects ",
            "JOIN current_subjects ON previous_subjects.student_id = current_subjects.student_id ",
            "WHERE ",
            "previous_subject &lt;&gt; current_subject ",
            "GROUP BY ",
            "previous_subject, ",
            "current_subject  ",
            "ORDER BY ",
            "previous_subject,current_subject;",
            "</script>"})
    public abstract List<OrderDetail> subjectChange();



    /**
     * 获取学生报名数据，例：秋季班，寒假班是否有报名
     * @return
     */
    @Select({"<script>",
            "SELECT",
                    "count(t1.id) as count",
                    "FROM",
                    "order_detail AS t1",
                    "LEFT JOIN `quarter` AS t2 ON t1.year_part = t2.year_part ",
                    "AND t1.quarter_num = t2.num ",
                    "WHERE",
                    "t2.id &gt;= ${quarterIdStart} ",
                    "AND t2.id  &lt;= ${quarterIdEnd} ",
                    "AND t1.`status` &lt;&gt; 2 ",
                    "AND t1.deleted = 0 ",
                    "and (t1.use_course_count&gt;0 or (t1.order_type = 1 and t1.all_course_count &lt;&gt; t1.out_house and t2.id =${quarterIdEnd}))",
                    "AND t1.lesson_type = ${lessonType} ",
                    "AND t1.subjects_id = ${subjectsId} ",
                    "<when test='moreStudent = 0 '>",
                        "AND t1.student_id in (select id from student where name = #{studentName}) ",
                    "</when>",
                    "<when test='moreStudent = 1 '>",
                    "AND t1.student_id = ${studentId} ",
                    "</when>",
                    "GROUP BY",
                    "student_id,",
                    "subjects_id",
    "</script>"})
    public abstract Long getStudentApplyCount(@Param("studentId") String studentId,
                                              @Param("studentName") String studentName,
                                              @Param("subjectsId") String subjectsId,
                                              @Param("lessonType") String lessonType,
                                              @Param("quarterIdStart") String quarterIdStart,
                                              @Param("quarterIdEnd") String quarterIdEnd,
                                              @Param("moreStudent") String moreStudent);

    /**
     * 判断其它科目两期内是否有报读
     *
     * @param studentId
     * @param studentName
     * @param subjectsId
     * @param lessonType
     * @param quarterIdStart
     * @param quarterIdEnd
     * @param moreStudent
     * @return
     */
    @Select({"<script>",
            "SELECT",
            "t1.subjects_id",
            "FROM",
            "order_detail AS t1",
            "LEFT JOIN `quarter` AS t2 ON t1.year_part = t2.year_part ",
            "AND t1.quarter_num = t2.num ",
            "WHERE",
            "t2.id &gt;= ${quarterIdStart} ",
            "AND t2.id  &lt;= ${quarterIdEnd} ",
            "AND t1.`status` &lt;&gt; 2 ",
            "AND t1.deleted = 0 ",
            "and (t1.use_course_count&gt;0 or (t1.order_type = 1 and t1.all_course_count &lt;&gt; t1.out_house and t2.id =${quarterIdEnd}))",
            //"and (t1.use_course_count&gt;0 or (t1.residue_course_count &gt; 0 and t2.id =${quarterIdEnd}))",
            "AND t1.lesson_type = ${lessonType} ",
            "AND t1.subjects_id &lt;&gt; ${subjectsId} ",
            "AND t1.student_id = ${studentId} ",
            "<when test='moreStudent = 0 '>",
                "AND t1.student_id in (select id from student where name = #{studentName}) ",
            "</when>",
            "<when test='moreStudent = 1 '>",
                "AND t1.student_id = ${studentId} ",
            "</when>",
            "GROUP BY",
            "student_id,",
            "subjects_id",
            "</script>"})
    public abstract List<Long> getStudentApplyCount2(@Param("studentId") String studentId,
                                              @Param("studentName") String studentName,
                                              @Param("subjectsId") String subjectsId,
                                              @Param("lessonType") String lessonType,
                                              @Param("quarterIdStart") String quarterIdStart,
                                              @Param("quarterIdEnd") String quarterIdEnd,
                                              @Param("moreStudent") String moreStudent);

    /**
     * 判断当前科目剩余课次是否大于15次课,
     *
     * @param studentId
     * @param studentName
     * @param subjectsId
     * @param lessonType
     * @param quarterIdStart
     * @param quarterIdEnd
     * @param moreStudent
     * @return 为1有招生信息，0为没有招生信息
     */
    @Select({"<script>",
            "SELECT ",
            "IF( count = pass_count, 1, 0 ) AS rs_data ",
            "FROM (",
            "SELECT ",
            "count( id ) AS count, ",
            "SUM( CASE WHEN residue_course_count &gt;= 45 THEN 1 ELSE 0 END ) AS pass_count ",
            "FROM",
            "(",
            "SELECT",
            "t1.id,",
            "SUM( t1.residue_course_count ) AS residue_course_count ",
            "FROM ",
            "order_detail as t1 ",
            "LEFT JOIN `quarter` AS t2 ON t1.year_part = t2.year_part ",
            "AND t1.quarter_num = t2.num ",
            "WHERE ",
            "t2.id &gt;= ${quarterIdStart} ",
            "AND t2.id  &lt;= ${quarterIdEnd} ",
            "and t1.lesson_type = ${lessonType} ",
            "AND t1.subjects_id = ${subjectsId}  ",
            "AND t1.`status` &lt;&gt; 2 ",
            "AND t1.deleted = 0 ",
            "<when test='moreStudent = 0 '>",
                "AND t1.student_id in (select id from student where name = #{studentName}) ",
            "</when>",
            "<when test='moreStudent = 1 '>",
                "AND t1.student_id = ${studentId} ",
            "</when>",
            "AND t1.residue_course_count &gt; 0 ",
            "GROUP BY t1.subjects_id ",
            ") AS t01 ",
            ") AS t",
            "</script>"})
    public abstract Long getStudentApplyCount3(@Param("studentId") String studentId,
                                               @Param("studentName") String studentName,
                                               @Param("subjectsId") String subjectsId,
                                               @Param("lessonType") String lessonType,
                                               @Param("quarterIdStart") String quarterIdStart,
                                               @Param("quarterIdEnd") String quarterIdEnd,
                                               @Param("moreStudent") String moreStudent);


    /**
     * 同时报名多期人数
     * @param queryWrapper
     * @return
     */
    @Select("SELECT COUNT( DISTINCT student_id ) AS numStudents FROM order_detail ${ew.customSqlSegment}")
    public abstract Integer simultaneouslyCountStudent(@Param(Constants.WRAPPER) QueryWrapper queryWrapper);

    /**
     * 同时报科目人数
     * @return
     */
    @Select({"<script>",
            "SELECT ",
            "COUNT( CASE WHEN num_subjects = 2 THEN 1 END ) AS two_subjects,",
            "COUNT( CASE WHEN num_subjects = 3 THEN 1 END ) AS three_subjects ",
            "FROM ",
            "( ",
            "SELECT ",
            "sd.id AS student_id, ",
            "COUNT( DISTINCT od.subjects_id ) AS num_subjects ",
            "FROM ",
            "student sd ",
            "LEFT JOIN order_detail od ON sd.id = od.student_id ",
            "WHERE ",
            "sd.deleted = 0  ",
            "AND od.deleted = 0 ",
            "AND od.quarter_num = ${quarterNum} ",
            "AND od.year_part = ${yearPart} ",
            "AND lesson_type = 1 ",
            "AND sd.id IN ( SELECT student_id FROM order_apply WHERE deleted = 0 AND `status` &lt;&gt; 2 AND quarter_num = ${quarterNum} AND year_part = ${yearPart} GROUP BY student_id ) ",
            "AND sd.id IN ( SELECT student_id FROM enroll_info ) ",
            "AND EXISTS ( ",
            "SELECT 1 ",
            "FROM order_detail od2 ",
            "WHERE od.student_id = od2.student_id ",
            "AND od.subjects_id &lt;&gt; od2.subjects_id ",
            "AND DATE(od.handle_date) = DATE(od2.handle_date) ",
            ") ",
            "GROUP BY ",
            "sd.id ",
            ") AS subquery;",
            "</script>"})
    public abstract List<OrderDetail> simultaneouslyCountStudentsBySubject(@Param("quarterNum") String quarterNum, @Param("yearPart") String yearPart);

    @Select("SELECT o.id,o.student_id,s.`name` AS studentName,o.year_class_id,o.subjects_id,sg.grade_id AS gradeId,g.`name` AS gradeName,g.teacher_id AS teacherId,getStaffNameForId(g.teacher_id) AS teacherName,o.reading_status,o.school_id,getSchoolName(o.school_id) AS school_name,SUM( CASE WHEN o.`status` <> 2 THEN o.residue_course_count ELSE 0 END ) AS residue_course_count  FROM order_detail AS o LEFT JOIN student AS s ON o.student_id = s.id  LEFT JOIN student_grade AS sg ON sg.student_id = s.id AND sg.year_part = o.year_part AND sg.quarter_num = o.quarter_num AND sg.year_class_id = o.year_class_id AND sg.lesson_type =o.lesson_type AND sg.school_id = o.school_id AND sg.subjects_id = o.subjects_id and sg.is_last=1 LEFT JOIN grade AS g ON sg.grade_id = g.id  ${ew.customSqlSegment} ")
    public abstract IPage<OrderDetail> changeSchoolStudents(IPage<?> page, @Param(Constants.WRAPPER) Wrapper wrapper);

    @Select("SELECT student_id FROM (SELECT o.id,o.student_id,s.`name` AS studentName,o.year_class_id,o.subjects_id,sg.grade_id AS gradeId,g.`name` AS gradeName,g.teacher_id AS teacherId,getStaffNameForId(g.teacher_id) AS teacherName,o.reading_status,o.school_id,getSchoolName(o.school_id) AS school_name,SUM( CASE WHEN o.`status` <> 2 THEN o.residue_course_count ELSE 0 END ) AS residue_course_count  FROM order_detail AS o LEFT JOIN student AS s ON o.student_id = s.id  LEFT JOIN student_grade AS sg ON sg.student_id = s.id AND sg.year_part = o.year_part AND sg.quarter_num = o.quarter_num AND sg.year_class_id = o.year_class_id AND sg.lesson_type =o.lesson_type AND sg.school_id = o.school_id AND sg.subjects_id = o.subjects_id and sg.is_last=1 LEFT JOIN grade AS g ON sg.grade_id = g.id  ${ew.customSqlSegment} ) AS a")
    public abstract List<OrderDetail> changeSchoolOnlyStudentIds(@Param(Constants.WRAPPER) Wrapper wrapper);

    @Select({"<script>",
             "SELECT ",
             "student_id,school_id,subjects_id,",
             "getSchoolName(school_id) AS schoolName,",
             "getSubjectsName(subjects_id) AS subjectsName ",
             "FROM ",
             "order_detail ",
             "WHERE ",
             "deleted = 0  ",
             "AND quarter_num = ${quarterNum} ",
             "AND year_part = ${yearPart} ",
             "AND lesson_type = 1 ",
             "AND reading_status = 1 ",
             "AND residue_course_count &gt; 0 ",
             "AND student_id IN ( SELECT student_id FROM order_apply WHERE deleted = 0 AND `status` &lt;&gt; 2 AND quarter_num = ${quarterNum} AND year_part = ${yearPart} )  ",
             "AND student_id IN ( SELECT student_id FROM student_grade WHERE deleted = 0 AND `status` = 1 AND quarter_num = ${quarterNum} AND year_part = ${yearPart} AND order_detail_id = order_detail.id ) ",
             "AND student_id = ${studentId} ",
             "GROUP BY subjects_id ",
             "ORDER BY subjects_id ",
             "</script>"})
    public abstract List<OrderDetail> studentIdBySubjectsId(@Param("quarterNum") String quarterNum, @Param("yearPart") String yearPart, @Param("studentId") String studentId);


    /**
     * 获取最后一条为转校订单
     * @param yearPart
     * @param quarterNum
     * @param lessonType
     * @return
     */
    @Select({"<script>",
            "SELECT",
                    "t1.* ",
                    "FROM",
                    "order_detail AS t1",
                    "INNER JOIN (",
                    "SELECT",
                      "MAX( id ) AS id ",
                    "FROM",
                      "order_detail ",
                    "WHERE",
                      "year_part = ${yearPart} ",
                      "AND quarter_num = ${quarterNum} ",
                      "AND lesson_type = ${lessonType} ",
                      "AND STATUS &lt;&gt; 2 ",
                      "AND deleted = 0 ",
                    "<when test='schoolId!=null and schoolId!=&apos;&apos; '>",
                    "  and school_id = #{schoolId}",
                    "</when>",
                    "GROUP BY",
                      "school_id,",
                      "student_id,",
                      "subjects_id ",
                    ") AS t2 ON t1.id = t2.id ",
                    "WHERE",
                    "t1.turn_out_house &gt; 0;",
            "</script>"})
    public abstract List<OrderDetail> getTransferOrderDetail(@Param("yearPart") Integer yearPart,
                                                     @Param("quarterNum") Integer quarterNum,
                                                     @Param("schoolId") String schoolId,
                                                     @Param("lessonType") Integer lessonType);

    @Select("select t1.* from order_detail as t1 INNER JOIN (select MAX(id) as id from order_detail where `status`<>2 and deleted =0 and lesson_type=1  GROUP BY student_id) as t2 on t1.id = t2.id ORDER BY t1.year_and_quarter desc")
    public abstract List<OrderDetail> getLastOrderListForStudent();

    /**
     * 交费率使用
     * 获取课时人数
     * @return
     */
    @Select({"<script>",
            "SELECT",
            "count( id ) AS studentAllCount,",
            "SUM( CASE WHEN residue_course_count_all &gt;= ${feeCourseNum} THEN 1 ELSE 0 END ) AS bigFifteen,",
            "SUM( CASE WHEN residue_course_count_all  &lt; ${feeCourseNum} THEN 1 ELSE 0 END ) AS littleFifteen,",
            "teacher_id,",
            "teacherName,",
            "school_id,",
            "getSchoolName(school_id) as schoolName,",
            "subjects_id,",
            "getSubjectsName(subjects_id) as subjects_name,",
            "(",
            "SELECT",
            "IFNULL( sum( CASE WHEN num &gt;= ${periodsNum} THEN 1 ELSE 0 END ), 0 ) AS all_num",
            "FROM",
            "( SELECT sum( t01.num ) AS num FROM rate_pay_view as t01",
            "LEFT JOIN rade_exclude_student as t07 on t01.student_id = t07.student_id and t07.classify_id = 1 and t07.deleted=0 and t07.type =1 and t01.year_part = t07.year_part and t01.quarter_num = t07.quarter_num and (FIND_IN_SET(t01.subjects_id,t07.subjects_ids) or t07.subjects_ids is null) and (FIND_IN_SET(t01.school_id,t07.school_ids) or t07.school_ids is null)",
            "WHERE",
            "t01.deleted =0 and t01.status=1",
            "and t07.id is null",
            "and t01.year_part = ${payYearPart}",
            "AND t01.quarter_num = ${payQuarterNum}",
            "and t01.lesson_type = t3.lesson_type",
            "<when test='yearClassId!=null and yearClassId!=&apos;&apos; '>",
            "  and t01.year_class_id in(${yearClassId})",
            "</when>",
            "<when test='classTypeId!=null and classTypeId!=&apos;&apos; '>",
            "  and t01.course_type_id in(${classTypeId})",
            "</when>",
            "<when test='startDate!=null and startDate!=&apos;&apos;'>",
            "and t01.add_date &gt;= #{startDate}",
            "</when>",
            "<when test='endDate!=null and endDate!=&apos;&apos;'>",
            "and t01.add_date &lt;= #{endDate}",
            "</when>",
            "<when test='yearClassId==null or yearClassId==&apos;&apos; '>",
            "<choose>",
            "<when test='quarterNum == 2'>",
            "  and t01.year_class_id &lt; 10",
//            "  and t01.year_class_id &lt; 13",
            //"  and year_class_id &lt;&gt; 6",
            "</when>",
            "<otherwise>",
            "  and t01.year_class_id &lt; 10",
//            "  and t01.year_class_id &lt; 13",
            //"  and year_class_id &lt;&gt; 6",
            "</otherwise>",
            "</choose>",
            "</when>",
            "<when test='type == 1 or type == 3'>",
            "and t01.teacher_id = t3.teacher_id",
            "</when>",
            "<when test='type == 2'>",
            "and school_id = t3.school_id",
            "and t01.teacher_id = t3.teacher_id",
            "</when>",
            "<when test='type == 4'>",
            "and t01.school_id = t3.school_id",
            "</when>",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos; '>",
            "and t01.subjects_id = t3.subjects_id",
            "</when>",
            "GROUP BY",
            "t01.teacher_id,",
            "t01.student_id,",
            "t01.subjects_id ",
            ") AS t_rate_pay",
            ")",
            " AS payNum",
            "FROM",
            "(",
            "SELECT",
            "id,",
            "school_id,",
            "teacher_id,",
            "student_id,",
            "subjects_id,",
            "year_part,",
            "quarter_num,",
            "grade_id,",
            "lesson_type,",
            "year_class_id,",
            "course_type_id,",
            "residue_course_count_all,teacherName",
            "FROM",
            "(",
            "SELECT",
            "t1.*,sum( IFNULL(t2.residue_course_count,0) + IFNULL(t6.all_course_count,0)) AS residue_course_count_all,sff.`name` AS teacherName",
            "FROM",
            "student_grade_view AS t1",
            "INNER JOIN staff AS sff ON sff.id = t1.teacher_id",
            "LEFT JOIN",
            "<when test='isHistory == 0'>",
            "order_detail AS t2",
            "</when>",
            "<when test='isHistory == 1'>",
            "order_detail_history AS t2",
            "</when>",
            "ON t2.student_id = t1.student_id",
            "AND t2.school_id = t1.school_id",
            "AND t2.subjects_id = t1.subjects_id",
            "AND t2.year_part = t1.year_part",
            "AND t2.lesson_type = t1.lesson_type",
            "AND t2.quarter_num = t1.quarter_num",
            "AND t2.STATUS &lt;&gt; 2",
            "AND t2.deleted = 0",
            "LEFT JOIN order_detail AS t6 ON t6.student_id = t1.student_id",
            "AND t6.subjects_id = t1.subjects_id ",
            "AND t6.lesson_type = t1.lesson_type ",
            "AND t6.year_and_quarter > ${nowYearAndQuarter} ",
            "AND t6.year_and_quarter &lt;= ${nextYearAndQuarter} ",
            "AND t6.STATUS &lt;&gt; 2 ",
            "AND t6.deleted = 0 ",
            "AND t6.order_type = 1",
            "LEFT JOIN rade_exclude_student as t7 on t7.classify_id = 1 and t7.deleted=0 and t7.type =1 and t1.student_id = t7.student_id and t1.year_part = t7.year_part and t1.quarter_num = t7.quarter_num and (FIND_IN_SET(t1.subjects_id,t7.subjects_ids) or t7.subjects_ids is null) and (FIND_IN_SET(t1.school_id,t7.school_ids) or t7.school_ids is null)",
            "WHERE",
            "t7.id is null",
            "and t1.year_part = ${yearPart}",
            "AND t1.quarter_num = ${quarterNum}",
            "and t1.is_last = 1",
            "and t1.is_makeup = 0",
            "and t1.deleted = 0",
            "and t1.status = 1",
            "and t1.lesson_type = ${lessonType}",
            "and (t1.reading_status in(1,4,5,6) or (t1.reading_status=3 and t1.use_course_count&gt;0))",
            "<when test='yearClassId==null or yearClassId==&apos;&apos; '>",
            "<choose>",
            "<when test='quarterNum == 2'>",
            "  and t1.year_class_id &lt; 10",
//            "  and t1.year_class_id &lt; 13",
            //"  and t1.year_class_id &lt;&gt; 6",
            "</when>",
            "<otherwise>",
            "  and t1.year_class_id &lt; 10",
//            "  and t1.year_class_id &lt; 13",
            //"  and t1.year_class_id &lt;&gt; 6",
            "</otherwise>",
            "</choose>",
            "</when>",
            "<when test='yearClassId!=null and yearClassId!=&apos;&apos; '>",
            "  and t1.year_class_id in(${yearClassId})",
            "</when>",
            "<when test='classTypeId!=null and classTypeId!=&apos;&apos; '>",
            "  and t1.course_type_id in(${classTypeId})",
            "</when>",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos; '>",
            "  and t1.subjects_id IN (${subjectsId})",
            "</when>",
            "<when test='schoolId!=null and schoolId!=&apos;&apos; '>",
            "  and t1.school_id in(${schoolId})",
            "</when>",
            "<when test='trackId!=null and trackId!=&apos;&apos; and type ==1'>",
            "  and t1.teacher_id in(select sourse_id from track_detail where deleted = 0 and track_id =#{trackId})",
            "</when>",
            "<when test='trackId!=null and trackId!=&apos;&apos; and type ==4'>",
            "  and t1.school_id in(select sourse_id from track_detail where deleted = 0 and track_id =#{trackId})",
            "</when>",
            "<when test='type ==3'>",
            "  and t1.teacher_id in(select admin_id from role_admin where role_id = 1568160887369994242)",
            "</when>",
            "<when test='name!=null and name!=&apos;&apos; '>",
            "and sff.`name` LIKE '%${name}%'",
            "</when>",
            "GROUP BY",
            "t1.school_id,",
            "t1.student_id,",
            "t1.subjects_id,",
            "t1.teacher_id",
            ") AS t",
            ") AS t3",
            "<when test='type == 1 or type == 3'>",
            "GROUP BY teacher_id",
            "</when>",
            "<when test='type == 2'>",
            "GROUP BY school_id,teacher_id",
            "</when>",
            "<when test='type == 4'>",
            "GROUP BY school_id",
            "</when>",
            "</script>"})
    public abstract List<RenewClassRecord> getPayFee(@Param("schoolId") String schoolId,@Param("yearPart") Integer yearPart,@Param("quarterNum") Integer quarterNum,@Param("subjectsId") String subjectsId,@Param("type") Integer type,@Param("startDate") String startDate,@Param("endDate") String endDate,@Param("trackId") String trackId,@Param("isHistory") Integer isHistory,@Param("lessonType") Integer lessonType,@Param("nowYearAndQuarter") Integer nowYearAndQuarter,@Param("nextYearAndQuarter") Integer nextYearAndQuarter,@Param("payYearPart") Integer payYearPart,@Param("payQuarterNum") Integer payQuarterNum,@Param("yearClassId") String yearClassId,@Param("classTypeId") String classTypeId,@Param("feeCourseNum") Integer feeCourseNum,@Param("periodsNum") Integer periodsNum,@Param("name") String name);

    /**
     * 交费率使用
     * 获取课时人数
     * @return
     */
    /**
     * 交费率使用
     * 获取课时人数
     * @return
     */
    @Select({"<script>",
            "SELECT",
            "count( id ) AS studentAllCount,",
            "SUM( CASE WHEN residue_course_count &gt;= ${feeCourseNum} THEN 1 ELSE 0 END ) AS bigFifteen,",
            "SUM( CASE WHEN residue_course_count  &lt; ${feeCourseNum} THEN 1 ELSE 0 END ) AS littleFifteen,",
            "teacher_id,",
            "teacherName,",
            "school_id,",
            "getSchoolName(school_id) as schoolName,",
            "subjects_id,",
            "getSubjectsName(subjects_id) as subjects_name, ",
            "(",
            "SELECT",
            "IFNULL( sum( CASE WHEN num &gt;= ${periodsNum} THEN 1 ELSE 0 END ), 0 ) AS all_num",
            "FROM",
            "( SELECT sum( t01.num ) AS num FROM rate_pay_view as t01",
            "LEFT JOIN rade_exclude_student as t07 on t07.classify_id = 1 and t07.deleted = 1 and t07.type =1 and t01.student_id = t07.student_id and t01.year_part = t07.year_part and t01.quarter_num = t07.quarter_num and (FIND_IN_SET(t01.subjects_id,t07.subjects_ids) or t07.subjects_ids is null) and (FIND_IN_SET(t01.school_id,t07.school_ids) or t07.school_ids is null)",
            "WHERE",
            "t01.deleted =0 and t01.status=1",
            "and t07.id is null",
            "and t01.year_part = ${yearPart} ",
            "AND t01.quarter_num = ${quarterNum} ",
            "and t01.lesson_type = ${lessonType} ",
            "<when test='yearClassId!=null and yearClassId!=&apos;&apos; '>",
            "  and t01.year_class_id in(${yearClassId})",
            "</when>",
            "<when test='classTypeId!=null and classTypeId!=&apos;&apos; '>",
            "  and t01.course_type_id in(${classTypeId})",
            "</when>",
            "<when test='startDate!=null and startDate!=&apos;&apos;'>",
            "and t01.add_date &gt;= #{startDate}",
            "</when>",
            "<when test='endDate!=null and endDate!=&apos;&apos;'>",
            "and t01.add_date &lt;= #{endDate}",
            "</when>",
            "<when test='yearClassId==null or yearClassId==&apos;&apos; '>",
            "<choose>",
            "<when test='quarterNum == 2'>",
            "  and t01.year_class_id &lt; 10",
//            "  and t01.year_class_id &lt; 13",
            //"  and year_class_id &lt;&gt; 6",
            "</when>",
            "<otherwise>",
            "  and t01.year_class_id &lt; 10",
//            "  and t01.year_class_id &lt; 13",
            //"  and year_class_id &lt;&gt; 6",
            "</otherwise>",
            "</choose>",
            "</when>",
            "<when test='type == 1 or type == 3'>",
            "and t01.teacher_id = t1.teacher_id",
            "</when>",
            "<when test='type == 2'>",
            "and t01.school_id = t1.school_id",
            "and t01.teacher_id = t1.teacher_id",
            "</when>",
            "<when test='type == 4'>",
            "and t01.school_id = t1.school_id",
            "</when>",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos; '>",
            "and t01.subjects_id = t1.subjects_id",
            "</when>",
            "GROUP BY",
            "t01.teacher_id,",
            "t01.student_id,",
            "t01.subjects_id ",
            ") AS t_rate_pay",
            ")",
            " AS payNum",
            "FROM ",
            "( ",
            "SELECT ",
            "t1.*,sff.`name` AS teacherName",
            "FROM ",
            "`student_residue_course` AS t1 ",
            "INNER JOIN (select id,`name` from staff) AS sff ON sff.id = t1.teacher_id",
            "LEFT JOIN grade AS t2 ON t1.grade_id = t2.id ",
            "LEFT JOIN rade_exclude_student as t7 on t7.classify_id = 1 and t7.deleted=0 and t7.type =1 and t1.student_id = t7.student_id and t1.year_part = t7.year_part and t1.quarter_num = t7.quarter_num and (FIND_IN_SET(t1.subjects_id,t7.subjects_ids) or t7.subjects_ids is null) and (FIND_IN_SET(t1.school_id,t7.school_ids) or t7.school_ids is null)",
            "WHERE ",
            "t1.year_part = ${yearPart} ",
            "AND t1.quarter_num = ${quarterNum} ",
            "AND t1.lesson_type = ${lessonType} ",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos; '>",
            "AND t1.subjects_id IN (${subjectsId}) ",
            "</when>",
            "<when test='type ==3'>",
            "AND t1.teacher_id in(select admin_id from role_admin where role_id = 1568160887369994242) ",
            "</when>",
            "AND '${endDate}' BETWEEN t1.start_date and t1.end_date",
            "<when test='yearClassId!=null and yearClassId!=&apos;&apos; '>",
            "  and t2.year_class_id in(${yearClassId})",
            "</when>",
            "<when test='yearClassId==null or yearClassId==&apos;&apos; '>",
            "<choose>",
            "<when test='quarterNum == 2'>",
            "  AND t2.year_class_id &lt; 10",
//            "  AND t2.year_class_id &lt; 13",
            //"  and t2.year_class_id &lt;&gt; 6",
            "</when>",
            "<otherwise>",
            "  AND t2.year_class_id &lt; 10",
//            "  AND t2.year_class_id &lt; 13",
            //"  and t2.year_class_id &lt;&gt; 6",
            "</otherwise>",
            "</choose>",
            "</when>",
            "<when test='schoolId!=null and schoolId!=&apos;&apos; '>",
            "AND t1.school_id in(${schoolId})",
            "</when>",
            "<when test='classTypeId!=null and classTypeId!=&apos;&apos; '>",
            "  AND t2.course_type_id in(${classTypeId})",
            "</when>",
            "<when test='trackId!=null and trackId!=&apos;&apos; and type ==1'>",
            "  AND t1.teacher_id in(select sourse_id from track_detail where deleted = 0 and track_id =#{trackId})",
            "</when>",
            "<when test='trackId!=null and trackId!=&apos;&apos; and type ==4'>",
            " and t1.school_id in(select sourse_id from track_detail where deleted = 0 and track_id =#{trackId})",
            "</when>",
            "and t7.id is null",
            "<when test='name!=null and name!=&apos;&apos; '>",
            "and sff.`name` LIKE '%${name}%'",
            "</when>",
            ") AS t1",
            "<when test='type == 1 or type == 3'>",
            "GROUP BY teacher_id ",
            "</when>",
            "<when test='type == 2'>",
            "GROUP BY school_id,teacher_id ",
            "</when>",
            "<when test='type == 4'>",
            "GROUP BY school_id ",
            "</when>",
            "</script>"})
    public abstract List<RenewClassRecord> getPayFeeByHistory(@Param("schoolId") String schoolId,@Param("yearPart") Integer yearPart,@Param("quarterNum") Integer quarterNum,@Param("subjectsId") String subjectsId,@Param("type") Integer type,@Param("startDate") String startDate,@Param("endDate") String endDate,@Param("trackId") String trackId,@Param("lessonType") Integer lessonType,@Param("yearClassId") String yearClassId,@Param("classTypeId") String classTypeId,@Param("feeCourseNum") Integer feeCourseNum,@Param("periodsNum") Integer periodsNum,@Param("name") String name);



    @Select("select t1.student_id from order_apply as t1 INNER JOIN order_transfer as t2 on t1.id = t2.order_id where t1.year_part = 2024 and t1.quarter_num =4 and t2.out_school_id = 1600459329282809857 \n" +
            "and t1.order_type = 4 and t1.apply_type = 5 GROUP BY t1.student_id")
    public List<Long> getChangeSchool();


    @Select({"<script>",
            "SELECT",
            "SUM( price ) AS price,",
            "SUM( CASE WHEN pay_num = 0 THEN 1 ELSE 0 END ) AS pay_num0,",
            "SUM( CASE WHEN pay_num = 0 THEN price ELSE 0 END ) AS pay_price0,",
            "SUM( CASE WHEN pay_num = 1 THEN 1 ELSE 0 END ) AS pay_num1,",
            "SUM( CASE WHEN pay_num = 1 THEN price ELSE 0 END ) AS pay_price1,",
            "SUM( CASE WHEN pay_num = 2 THEN 1 ELSE 0 END ) AS pay_num2,",
            "SUM( CASE WHEN pay_num = 2 THEN price ELSE 0 END ) AS pay_price2",
            "FROM (",
            "SELECT",
            "IFNULL( SUM( price ), 0 ) AS price,",
            "CASE",
            "WHEN SUM( all_course_count ) &gt;= 45 AND SUM( all_course_count ) &lt; 90 THEN 1",
            "WHEN SUM( all_course_count ) &gt;= 90 THEN 2",
            "ELSE 0 ",
            "END AS pay_num ",
            "FROM (",
            "SELECT *,order_id AS order_id_param",
            "FROM",
            "income_day_report_view ",
            "${ew.customSqlSegment}",
            ") AS t1 ",
            "WHERE",
            "subjects_id = t1.subjects_id ",
            "AND student_id = t1.student_id ",
            "GROUP BY",
            "subjects_id,student_id,course_id",
            ") AS t01",
            "</script>"})
    public abstract IncomeDailyRs getIncomeDailyRsCount(@Param(Constants.WRAPPER) Wrapper wrapper);


}
